import collections
import json
import time

from django.conf import settings
from django.contrib.auth.models import User
from django.db import transaction, connection
from django.db.utils import IntegrityError
from django.shortcuts import get_object_or_404, redirect
from django_filters.rest_framework import DjangoFilterBackend
from django.db.models import Count, F, Q
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render

from libcloud.base import DriverType, get_driver
from libcloud.storage.types import ContainerDoesNotExistError, ObjectDoesNotExistError
from rest_framework import generics, filters, status
from rest_framework.exceptions import ParseError, ValidationError
from rest_framework.permissions import IsAuthenticated, IsAuthenticatedOrReadOnly
from rest_framework.response import Response
from rest_framework.views import APIView
from rest_framework.parsers import MultiPartParser
from rest_framework_csv.renderers import CSVRenderer

from .filters import DocumentFilter
from .models import Project, Label, Document, RoleMapping, Role, SequenceAnnotation, DocumentAnnotation
from .permissions import IsProjectAdmin, IsAnnotatorAndReadOnly, IsAnnotator, IsAnnotationApproverAndReadOnly, IsOwnAnnotation, IsAnnotationApprover
from .serializers import ProjectSerializer, LabelSerializer, DocumentSerializer, UserSerializer, ApproverSerializer
from .serializers import ProjectPolymorphicSerializer, RoleMappingSerializer, RoleSerializer
from .utils import CSVParser, ExcelParser, JSONParser, PlainTextParser, CoNLLParser, AudioParser, iterable_to_io, \
    dictfetchall
from .utils import JSONLRenderer
from .utils import JSONPainter, CSVPainter

from .models import cal

IsInProjectReadOnlyOrAdmin = (IsAnnotatorAndReadOnly | IsAnnotationApproverAndReadOnly | IsProjectAdmin)
IsInProjectOrAdmin = (IsAnnotator | IsAnnotationApprover | IsProjectAdmin)


class Health(APIView):
    permission_classes = (IsAuthenticatedOrReadOnly,)

    def get(self, request, *args, **kwargs):
        return Response({'status': 'green'})


class Me(APIView):
    permission_classes = (IsAuthenticated,)

    def get(self, request, *args, **kwargs):
        serializer = UserSerializer(request.user, context={'request': request})
        return Response(serializer.data)


class Features(APIView):
    permission_classes = (IsAuthenticated,)

    def get(self, request, *args, **kwargs):
        return Response({
            'cloud_upload': bool(settings.CLOUD_BROWSER_APACHE_LIBCLOUD_PROVIDER),
        })


class ProjectList(generics.ListCreateAPIView):
    serializer_class = ProjectPolymorphicSerializer
    pagination_class = None
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]

    def get_queryset(self):
        return self.request.user.projects

    def perform_create(self, serializer):
        serializer.save(users=[self.request.user])


class ProjectDetail(generics.RetrieveUpdateDestroyAPIView):
    queryset = Project.objects.all()
    serializer_class = ProjectSerializer
    lookup_url_kwarg = 'project_id'
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]


class StatisticsAPI(APIView):
    pagination_class = None
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]

    def get(self, request, *args, **kwargs):
        p = get_object_or_404(Project, pk=self.kwargs['project_id'])

        include = set(request.GET.getlist('include'))
        response = {}

        if not include or 'label' in include:
            label_count, user_count = self.label_per_data(p)
            response['label'] = label_count
            # TODO: Make user_label count chart
            response['user_label'] = user_count

        if not include or 'total' in include or 'remaining' in include or 'user' in include:
            progress = self.progress(project=p)
            response.update(progress)

        if include:
            response = {key: value for (key, value) in response.items() if key in include}

        return Response(response)

    @staticmethod
    def _get_user_completion_data(annotation_class, annotation_filter):
        all_annotation_objects  = annotation_class.objects.filter(annotation_filter)
        set_user_data = collections.defaultdict(set)
        for ind_obj in all_annotation_objects.values('user__username', 'document__id'):
            set_user_data[ind_obj['user__username']].add(ind_obj['document__id'])
        return {i: len(set_user_data[i]) for i in set_user_data}


    def progress(self, project):
        docs = project.documents
        annotation_class = project.get_annotation_class()
        total = docs.count()
        annotation_filter = Q(document_id__in=docs.all())
        user_data = self._get_user_completion_data(annotation_class, annotation_filter)
        if not project.collaborative_annotation:
            annotation_filter &= Q(user_id=self.request.user)
        done = annotation_class.objects.filter(annotation_filter)\
            .aggregate(Count('document', distinct=True))['document__count']
        remaining = total - done
        return {'total': total, 'remaining': remaining, 'user': user_data}

    def label_per_data(self, project):
        annotation_class = project.get_annotation_class()
        return annotation_class.objects.get_label_per_data(project=project)


class ApproveLabelsAPI(APIView):
    permission_classes = [IsAuthenticated & (IsAnnotationApprover | IsProjectAdmin)]

    def post(self, request, *args, **kwargs):
        approved = self.request.data.get('approved', True)
        document = get_object_or_404(Document, pk=self.kwargs['doc_id'])
        document.annotations_approved_by = self.request.user if approved else None
        document.save()
        return Response(ApproverSerializer(document).data)


class LabelList(generics.ListCreateAPIView):
    serializer_class = LabelSerializer
    pagination_class = None
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]

    def get_queryset(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        return project.labels

    def perform_create(self, serializer):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        serializer.save(project=project)


class LabelDetail(generics.RetrieveUpdateDestroyAPIView):
    queryset = Label.objects.all()
    serializer_class = LabelSerializer
    lookup_url_kwarg = 'label_id'
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]


class DocumentList(generics.ListCreateAPIView):
    serializer_class = DocumentSerializer
    filter_backends = (DjangoFilterBackend, filters.SearchFilter, filters.OrderingFilter)
    search_fields = ('text', )
    ordering_fields = ('created_at', 'updated_at', 'doc_annotations__updated_at',
                       'seq_annotations__updated_at', 'seq2seq_annotations__updated_at')
    filter_class = DocumentFilter
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]

    def get_queryset(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        quality_status = self.request.GET.get('quality_status')
        number = 0
        sessionCurrentPage = self.request.GET.get('sessionCurrentPage')
        # cd_type = self.request.GET.get('type')
        userId = self.request.GET.get('userId')
        if int(userId) != 0:
            role = RoleMapping.objects.get(user_id=int(userId), project_id=int(self.kwargs['project_id']))
            if role.role_id == 2:
                userId = 0
        queryset = project.documents
        if int(userId) != 0:
            if self.request.GET.get('doc_annotations__isnull') == 'true' or self.request.GET.get('seq_annotations__isnull') == 'true':
                queryset = queryset.filter(user_id=userId)
            else:
                queryset = queryset.filter(Q(user_id=userId) | Q(user_id__isnull=True))
            # queryset = queryset.filter(user_id=self.kwargs['user_id'])
        if project.randomize_document_order:
            if int(userId) != 0:
                prodject_flg = Project.objects.get(id=self.kwargs['project_id'])
                # 获取分配数量
                docs_number_by_me = Document.objects.filter(user_id=userId,
                                                            project_id=self.kwargs['project_id'])
                number = docs_number_by_me.count()

                # 简单
                if prodject_flg.project_type == 'DocumentClassification':
                    # work_number = Document.objects.raw(
                    #     "SELECT DISTINCT api_document.id FROM api_document ,api_documentannotation WHERE api_document.project_id = %s AND api_document.id = api_documentannotation.document_id AND api_documentannotation.user_id = %s",
                    #     [int(self.kwargs['project_id']), int(userId)])
                    if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and (int(sessionCurrentPage) == int(number) or (
                            int(number) - int(number)%10 <= int(sessionCurrentPage) <= int(number) + 10) or int(number) == 0):
                        cursor = connection.cursor()
                        cursor.execute(
                            "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id %% %s ASC LIMIT 10",
                            [int(userId), int(self.kwargs['project_id']), int(self.request.user.id)])
                        cursor.close
                    if quality_status is not None:
                        queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).filter(
                            quality_status=quality_status).order_by('sort_id')
                    else:
                        queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).order_by('sort_id')
                #     复杂
                else:
                    # work_number = Document.objects.raw(
                    #     "SELECT DISTINCT api_document.id FROM api_document ,api_sequenceannotation WHERE api_document.project_id = %s AND api_document.id = api_sequenceannotation.document_id AND api_sequenceannotation.user_id = %s",
                    #     [int(self.kwargs['project_id']), int(userId)])

                    # if int(userId) != 0 and len(docs_number_by_me) == len(
                    #         work_number):
                    # if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and int(sessionCurrentPage) >= int(number) and int(sessionCurrentPage) - int(number) < 10:
                    if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and (
                            int(sessionCurrentPage) == int(number) or (
                            int(number) - int(number) % 10 <= int(sessionCurrentPage) <= int(number) + 10) or int(
                        number) == 0):
                        cursor = connection.cursor()
                        cursor.execute(
                            "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id %% %s ASC LIMIT 10",
                            [int(userId), int(self.kwargs['project_id']), int(self.request.user.id)])
                        cursor.close
                    if quality_status is not None:
                        queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).filter(
                            quality_status=quality_status).order_by('sort_id')
                    else:
                        queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).order_by('sort_id')
            # if int(userId) != 0 and len(docs_number_by_me) == len(
            #         work_number):
            # if int(userId) != 0 and (int(sessionCurrentPage) == int(number) or (
            #         int(number) < int(sessionCurrentPage) <= int(number) + 10) or int(number) == 0):
            #     cursor = connection.cursor()
            #     cursor.execute(
            #         "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id %% %s ASC LIMIT 10",
            #         [int(userId), int(self.kwargs['project_id']), int(self.request.user.id)])
            #     cursor.close
            # if quality_status is not None:
            #     queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).filter(
            #         quality_status=quality_status).order_by('sort_id')
            # else:
            #     queryset = queryset.annotate(sort_id=F('id') % self.request.user.id).order_by('sort_id')
        else:
            # 获取分配数量
            docs_number_by_me = Document.objects.filter(user_id=userId,
                                                        project_id=self.kwargs['project_id'])
            number = docs_number_by_me.count()
            if int(userId) != 0:
                prodject_flg = Project.objects.get(id=self.kwargs['project_id'])
                if prodject_flg.project_type == 'DocumentClassification':
                    # work_number = Document.objects.raw(
                    #     "SELECT DISTINCT api_document.id FROM api_document ,api_documentannotation WHERE api_document.project_id = %s AND api_document.id = api_documentannotation.document_id AND api_documentannotation.user_id = %s",
                    #     [int(self.kwargs['project_id']), int(userId)])
                    if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and (int(sessionCurrentPage) == int(number) or (
                            int(number) - int(number)%10 <= int(sessionCurrentPage) <= int(number) + 10) or int(number) == 0):
                        cursor = connection.cursor()
                        cursor.execute(
                            "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id ASC LIMIT 10",
                            [int(userId), int(self.kwargs['project_id'])])
                        cursor.close
                    if quality_status is not None:
                        queryset = queryset.filter(quality_status=quality_status).order_by('id')
                    else:
                        queryset = queryset.order_by('id')
                else:
                    # work_number = Document.objects.raw(
                    #     "SELECT DISTINCT api_document.id FROM api_document ,api_sequenceannotation WHERE api_document.project_id = %s AND api_document.id = api_sequenceannotation.document_id AND api_sequenceannotation.user_id = %s",
                    #     [int(self.kwargs['project_id']), int(userId)])
                    # if int(userId) != 0 and len(docs_number_by_me) == len(work_number):
                    # if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and int(sessionCurrentPage) >= int(number) and int(sessionCurrentPage) - int(
                    #         number) < 10:
                    if int(userId) != 0 and int(userId) != 1 and int(userId) != 3 and (int(sessionCurrentPage) == int(number) or (
                            int(number) - int(number)%10 <= int(sessionCurrentPage) <= int(number) + 10) or int(number) == 0):
                        cursor = connection.cursor()
                        cursor.execute(
                            "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id ASC LIMIT 10",
                            [int(userId), int(self.kwargs['project_id'])])
                        cursor.close
                    if quality_status is not None:
                        queryset = queryset.filter(quality_status=quality_status).order_by('id')
                    else:
                        queryset = queryset.order_by('id')

            # if int(userId) != 0 and len(docs_number_by_me) == len(work_number):
            # if int(userId) != 0 and (int(sessionCurrentPage) == int(number) or (
            #         int(number) < int(sessionCurrentPage) <= int(number) + 10) or int(number) == 0):
            #     cursor = connection.cursor()
            #     cursor.execute(
            #         "UPDATE api_document set user_id = %s,updated_at = NOW() WHERE project_id = %s AND user_id is null ORDER BY api_document.id ASC LIMIT 10",
            #         [int(userId), int(self.kwargs['project_id'])])
            #     cursor.close
            # if quality_status is not None:
            #     queryset = queryset.filter(quality_status=quality_status).order_by('id')
            # else:
            #     queryset = queryset.order_by('id')
        return queryset

    def perform_create(self, serializer):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        serializer.save(project=project)


class DocumentDetail(generics.RetrieveUpdateDestroyAPIView):
    queryset = Document.objects.all()
    serializer_class = DocumentSerializer
    lookup_url_kwarg = 'doc_id'
    permission_classes = [IsAuthenticated & IsInProjectReadOnlyOrAdmin]


class AnnotationList(generics.ListCreateAPIView):
    pagination_class = None
    permission_classes = [IsAuthenticated & IsInProjectOrAdmin]
    swagger_schema = None

    def get_serializer_class(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        self.serializer_class = project.get_annotation_serializer()
        return self.serializer_class

    def get_queryset(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        model = project.get_annotation_class()

        queryset = model.objects.filter(document=self.kwargs['doc_id'], label_id__isNull=False)
        if not project.collaborative_annotation:
            queryset = queryset.filter(user=self.request.user)

        return queryset

    def create(self, request, *args, **kwargs):
        self.check_single_class_classification(self.kwargs['project_id'], self.kwargs['doc_id'], request.user)
        request.data['document'] = self.kwargs['doc_id']
        try:
            if self.kwargs['doc_id'] is not None:
                # undistributedDocument = Document.objects.filter(id=self.kwargs['doc_id'],project_id=self.kwargs['project_id']).values(
                #     'user_id')
                # if undistributedDocument[0]['user_id'] is None:
                #     # return JsonResponse({'warning':'请不要跳页,操作不属于自己的数据'},status=status.HTTP_200_OK,safe=False)
                #     return HttpResponse(json.dumps({'warning':'请不要跳页,操作不属于自己的数据'}, ensure_ascii=False), content_type='application/json',status=status.HTTP_200_OK)
                docList = Document.objects.filter(id=self.kwargs['doc_id'], user_id=self.request.user)
                if docList.count() > 0:
                    cursor = connection.cursor()
                    cursor.execute(
                        "delete from api_documentannotation where document_id = %s",
                        [self.kwargs['doc_id']])
                    cursor.close()
        except Exception as ex:
            return HttpResponse(json.dumps({'error':ex}, ensure_ascii=False), content_type='application/json',status=status.HTTP_200_OK)
        return super().create(request, args, kwargs)

    def perform_create(self, serializer):
        docList = Document.objects.filter(id=self.kwargs['doc_id'], user_id=self.request.user)
        if docList.count() > 0:
            serializer.save(document_id=self.kwargs['doc_id'], user=self.request.user)

    @staticmethod
    def check_single_class_classification(project_id, doc_id, user):
        project = get_object_or_404(Project, pk=project_id)
        if not project.single_class_classification:
            return

        model = project.get_annotation_class()
        annotations = model.objects.filter(document_id=doc_id)
        if not project.collaborative_annotation:
            annotations = annotations.filter(user=user)

        if annotations.exists():
            raise ValidationError('requested to create duplicate annotation for single-class-classification project')


class AnnotationDetail(generics.RetrieveUpdateDestroyAPIView):
    lookup_url_kwarg = 'annotation_id'
    swagger_schema = None

    def get_permissions(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        if project.collaborative_annotation:
            self.permission_classes = [IsAuthenticated & IsInProjectOrAdmin]
        else:
            self.permission_classes = [IsAuthenticated & IsInProjectOrAdmin & IsOwnAnnotation]
        return super().get_permissions()

    def get_serializer_class(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        self.serializer_class = project.get_annotation_serializer()
        return self.serializer_class

    def get_queryset(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        model = project.get_annotation_class()
        self.queryset = model.objects.all()
        return self.queryset


class TextUploadAPI(APIView):
    parser_classes = (MultiPartParser,)
    permission_classes = [IsAuthenticated & IsProjectAdmin]

    def post(self, request, *args, **kwargs):
        if 'file' not in request.data:
            raise ParseError('Empty content')

        return self.save_file(
            user=request.user,
            file=request.data['file'],
            file_format=request.data['format'],
            project_id=kwargs['project_id'],
        )

    @classmethod
    def save_file(cls, user, file, file_format, project_id):
        project = get_object_or_404(Project, pk=project_id)
        parser = cls.select_parser(file_format)
        data = parser.parse(file)
        if file_format == 'column':
            if Document.objects.all().count() > 0:
                maxVersionInfo = Document.objects.latest('version')
                maxVersion = int(maxVersionInfo.version)
            else:
                maxVersion = 0
            for s in data:
                for a in s:
                    text = str(a.get("text"))
                    val = text.split('\t')
                    if len(val) != 2:
                        return Response(status=status.HTTP_400_BAD_REQUEST)
                    Document.objects.create(project_id=project_id, text=str(val[0]), meta='{}', version=maxVersion+1, doc_ann=str(val[1]))
        elif file_format == 'plain':
            if Document.objects.all().count() > 0:
                maxVersionInfo = Document.objects.latest('version')
                maxVersion = int(maxVersionInfo.version)
            else:
                maxVersion = 0
            for s in data:
                for a in s:
                    text = str(a.get("text"))
                    val = text.split('\t')
                    if len(val) != 1:
                        return Response(status=status.HTTP_400_BAD_REQUEST)
                    Document.objects.create(project_id=project_id, text=str(val[0]), meta='{}',
                                            version=maxVersion + 1)
        else:
            storage = project.get_storage(data)
            storage.save(user)
        return Response(status=status.HTTP_200_OK)
    @classmethod
    def select_parser(cls, file_format):
        if file_format == 'plain':
            return PlainTextParser()
        elif file_format == 'column':
            return PlainTextParser()
        elif file_format == 'csv':
            return CSVParser()
        elif file_format == 'json':
            return JSONParser()
        elif file_format == 'conll':
            return CoNLLParser()
        elif file_format == 'excel':
            return ExcelParser()
        elif file_format == 'audio':
            return AudioParser()
        else:
            raise ValidationError('format {} is invalid.'.format(file_format))


class CloudUploadAPI(APIView):
    permission_classes = TextUploadAPI.permission_classes

    def get(self, request, *args, **kwargs):
        try:
            project_id = request.query_params['project_id']
            file_format = request.query_params['upload_format']
            cloud_container = request.query_params['container']
            cloud_object = request.query_params['object']
        except KeyError as ex:
            raise ValidationError('query parameter {} is missing'.format(ex))

        try:
            cloud_file = self.get_cloud_object_as_io(cloud_container, cloud_object)
        except ContainerDoesNotExistError:
            raise ValidationError('cloud container {} does not exist'.format(cloud_container))
        except ObjectDoesNotExistError:
            raise ValidationError('cloud object {} does not exist'.format(cloud_object))

        TextUploadAPI.save_file(
            user=request.user,
            file=cloud_file,
            file_format=file_format,
            project_id=project_id,
        )

        next_url = request.query_params.get('next')

        if next_url == 'about:blank':
            return Response(data='', content_type='text/plain', status=status.HTTP_201_CREATED)

        if next_url:
            return redirect(next_url)

        return Response(status=status.HTTP_201_CREATED)

    @classmethod
    def get_cloud_object_as_io(cls, container_name, object_name):
        provider = settings.CLOUD_BROWSER_APACHE_LIBCLOUD_PROVIDER.lower()
        account = settings.CLOUD_BROWSER_APACHE_LIBCLOUD_ACCOUNT
        key = settings.CLOUD_BROWSER_APACHE_LIBCLOUD_SECRET_KEY

        driver = get_driver(DriverType.STORAGE, provider)
        client = driver(account, key)

        cloud_container = client.get_container(container_name)
        cloud_object = cloud_container.get_object(object_name)

        return iterable_to_io(cloud_object.as_stream())


class TextDownloadAPI(APIView):
    permission_classes = TextUploadAPI.permission_classes

    renderer_classes = (CSVRenderer, JSONLRenderer)

    def get(self, request, *args, **kwargs):
        format = request.query_params.get('q')
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        documents = project.documents.all()
        startTime = request.GET.get('startTime')
        endTime = request.GET.get('endTime')
        flag = True
        # 时间比对   startTime     endTime           操作
        #              1             0          查询当天数据
        #              1             1          查询时间段数据
        #              0             0          默认查询到当前时间所有数据
        if startTime is None or startTime == '':
            startTime = "1970-01-01 00:00:00"
        else:
            if endTime is None or endTime == '':
                startTime = startTime + " 00:00:00"
                endTime = startTime[:-8] + "23:59:59"
                flag = False

        if endTime is None or endTime == '' and flag:
            endTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        if format == 'txt':
            textData = []
            # 质检状态 2 合格  1 驳回  0 未质检
            # quality_status = 2
            # 普通标记文本
            if project.project_type == 'DocumentClassification':
                documents = DocumentAnnotation.objects.annotate(text=F('document__text'), labelName=F('label__text'),userName=F('user__username')).filter(document__project=self.kwargs['project_id'], updated_at__gte=startTime,updated_at__lte=endTime).values('text', 'labelName', 'userName')
                for d in documents:
                    textData.append(d['userName'] + "\t" + d['labelName'] + "\t" + d['text'] + "\n")
            # 复杂标记文本
            if project.project_type == 'SequenceLabeling':
                # 执行sql查询
                cursor = connection.cursor()
                cursor.execute("SELECT intent.text AS intentions, doc.text AS document, label.text AS labelName, seq.document_id AS documentId, seq.start_offset AS startOffset, seq.end_offset AS endOffset, authUser.username FROM api_sequenceannotation AS seq LEFT JOIN api_document doc ON doc.id = seq.document_id LEFT JOIN api_label label ON label.id = seq.label_id LEFT JOIN api_label intent ON intent.id = seq.label_id1 LEFT JOIN auth_user authUser ON authUser.id = seq.user_id WHERE doc.project_id = % s AND seq.updated_at BETWEEN % s AND % s",
                                     [self.kwargs['project_id'], startTime, endTime])
                documents = cursor.fetchall()
                cursor.close()
                documentsDict = {}
                # sql 数据查询结果 —> 意图,文本,标签,文本id,标签开始下标,标签结束下标
                # 把相同文本多种标签的放进字典里
                for d in documents:
                    if  str(d[3]) not in documentsDict.keys():
                        se = set()
                        se.add(d)
                        documentsDict[str(d[3])] = se
                    else:
                        documentsDict[str(d[3])].add(d)
                # 遍历相同的文本，输出对应格式
                for key in documentsDict.keys():
                    # 把集合转成列表根据下标取文本数据，初始化新列表长度
                    docSet = documentsDict[key]
                    docList = list(docSet)
                    # 定义好长度，用列表下标快速插入
                    slots = ['O']*len(docList[0][1])
                    document = ''
                    username = ''
                    for docDetail in documentsDict[key]:
                        username = docDetail[6]
                        if docDetail[2] is None or docDetail[2] == "":
                            intent = docDetail[0]
                            if document == "" and len(documentsDict[key]) == 1:
                                document = docDetail[1]
                        else:
                            localDocument = ''
                            # 文本英文判断  不加 |
                            if document == '':
                                for itemIndex in range(len(docDetail[1])):
                                    if docDetail[1][itemIndex].encode().isalpha():
                                        if chineseFlag:
                                            localDocument += '|' + docDetail[1][itemIndex]
                                            chineseFlag = False
                                        else:
                                            localDocument += docDetail[1][itemIndex]
                                            chineseFlag = False
                                    else:
                                        localDocument += '|' + docDetail[1][itemIndex]
                                        chineseFlag = True
                            if document == '':
                                document = localDocument[1:]
                            # 标签不是 O 时
                            subscript = docDetail[4]
                            if not docDetail[2] == 'O':
                                # 如果下标存储出现错误数据 默认最后下标为最大长度
                                if docDetail[5] - docDetail[4] <= len(docList[0][1]):
                                    strLength = docDetail[5] - docDetail[4]
                                else:
                                    strLength = len(docList[0][1]) - docDetail[4]
                                for item in range(strLength):
                                    if subscript == docDetail[4]:
                                        slotInfo = 'B-' + docDetail[2]
                                        slots[subscript] = slotInfo
                                    else:
                                        slotInfo = 'I-' + docDetail[2]
                                        slots[subscript] = slotInfo
                                    subscript += 1
                            # # 标签是 O 时
                            # else:
                            #     # 如果下标存储出现错误数据 默认最后下标为最大长度
                            #     if docDetail[5] - docDetail[4] <= len(docList[0][1]):
                            #         strLength = docDetail[5] - docDetail[4]
                            #     else:
                            #         strLength = len(docList[0][1]) - docDetail[4]
                            #     for item in range(strLength):
                            #         slots[subscript] = docDetail[2]
                    # 添加到文本信息
                    slot = "|".join(slots)
                    OCount = 0
                    for proofSlot in slots:
                        if proofSlot == 'O':
                            OCount += 1
                    if OCount == len(docList[0][1]):
                        textData.append(str(username) + "\t" + str(intent) + "\t" + str(document) + "\n")
                    else:
                        textData.append(str(username) + "\t" + str(intent) + "\t" + str(document) + "\t" + slot + "\n")
            return HttpResponse(textData,content_type='text/html')
        painter = self.select_painter(format)
        # json1 format prints text labels while json format prints annotations with label ids
        # json1 format - "labels": [[0, 15, "PERSON"], ..]
        # json format - "annotations": [{"label": 5, "start_offset": 0, "end_offset": 2, "user": 1},..]
        if format == "json1":
            labels = project.labels.all()
            data = JSONPainter.paint_labels(documents, labels)
        else:
            data = painter.paint(documents)
        return Response(data)

    def select_painter(self, format):
        if format == 'csv':
            return CSVPainter()
        elif format == 'json' or format == "json1":
            return JSONPainter()
        else:
            raise ValidationError('format {} is invalid.'.format(format))


class Users(APIView):
    permission_classes = [IsAuthenticated & IsProjectAdmin]

    def get(self, request, *args, **kwargs):
        queryset = User.objects.all()
        serialized_data = UserSerializer(queryset, many=True).data
        return Response(serialized_data)


class Roles(generics.ListCreateAPIView):
    serializer_class = RoleSerializer
    pagination_class = None
    permission_classes = [IsAuthenticated & IsProjectAdmin]
    queryset = Role.objects.all()


class RoleMappingList(generics.ListCreateAPIView):
    serializer_class = RoleMappingSerializer
    pagination_class = None
    permission_classes = [IsAuthenticated & IsProjectAdmin]

    def get_queryset(self):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        return project.role_mappings

    def perform_create(self, serializer):
        project = get_object_or_404(Project, pk=self.kwargs['project_id'])
        serializer.save(project=project)


class RoleMappingDetail(generics.RetrieveUpdateDestroyAPIView):
    queryset = RoleMapping.objects.all()
    serializer_class = RoleMappingSerializer
    lookup_url_kwarg = 'rolemapping_id'
    permission_classes = [IsAuthenticated & IsProjectAdmin]


class LabelUploadAPI(APIView):
    parser_classes = (MultiPartParser,)
    permission_classes = [IsAuthenticated & IsProjectAdmin]

    @transaction.atomic
    def post(self, request, *args, **kwargs):
        if 'file' not in request.data:
            raise ParseError('Empty content')
        self.save_file(
            user=request.user,
            file=request.data['file'],
            file_format='plain',
            project_id=kwargs['project_id'],
        )
        return Response(status=status.HTTP_201_CREATED)

    @classmethod
    def save_file(cls, user, file, file_format, project_id):
        parser = cls.select_parser(file_format)
        data = parser.parse(file)
        for s in data:
            for a in s:
                text = str(a.get("text"))
                if text.find('\t') > 0:
                    val = text.split('\t')
                    if len(val) == 4:
                        if str(val[3]).find("/") >= 0:
                            if str(val[1]).find("-") >= 0:
                                res = val[1].split('-')
                                res_val = res[1]
                                Label.objects.create(text=val[0], label_name=res_val, describe=str(val[1]),
                                                     project_id=project_id, p_text=val[2], slot_flg=False)
                            else:
                                Label.objects.create(text=val[0], label_name=str(val[1]), describe=str(val[1]),
                                                     project_id=project_id, p_text=val[2], slot_flg=False)
                        else:
                            if str(val[1]).find("-") >= 0:
                                res = val[1].split('-')
                                res_val = res[1]
                                Label.objects.create(text=val[0], label_name=res_val, describe=str(val[1]),
                                                 project_id=project_id, p_text=val[2])
                            else:
                                Label.objects.create(text=val[0], label_name=str(val[1]), describe=str(val[1]),
                                                     project_id=project_id, p_text=val[2])
                    if len(val) == 3:
                        if str(val[1]).find("-") >= 0:
                            res = val[1].split('-')
                            res_val = res[1]
                            Label.objects.create(text=val[0], label_name=res_val, describe=str(val[1]), project_id=project_id, p_text=val[2])
                        else:
                            Label.objects.create(text=val[0], label_name=str(val[1]),
                                                 describe=str(val[1]), project_id=project_id,
                                                 p_text=val[2])
                    if len(val) == 2:
                        Label.objects.create(text=val[0], label_name=val[1], describe=val[1], project_id=project_id)

    @classmethod
    def select_parser(cls, file_format):
        if file_format == 'plain':
            return PlainTextParser()
        elif file_format == 'csv':
            return CSVParser()
        elif file_format == 'json':
            return JSONParser()
        elif file_format == 'conll':
            return CoNLLParser()
        elif file_format == 'excel':
            return ExcelParser()
        elif file_format == 'audio':
            return AudioParser()
        else:
            raise ValidationError('format {} is invalid.'.format(file_format))


def Cal(request):
    value_a = 11
    value_b = 33
    result = int(value_a) + int(value_b)
    cal.objects.create(value_a=value_a, value_b=value_b, result=result)
    # print(value_a, value_b)
    return render(request, 'result.html', context={'data': result})


def index(request):
    return render(request, "index.html")


def Distributio(request):
    value_userId_distributio = request.GET.get('userId')
    userId_List = value_userId_distributio.split(",")
    value_number_distributio = request.GET.get('number')
    value_projectId_distributio = request.GET.get('projectId')
    cursor = connection.cursor()
    res = Document.objects.filter(user_id__isnull=True, project_id=value_projectId_distributio)[:int(float(value_number_distributio) * len(userId_List))]
    for userId in userId_List:
        ret = cursor.execute("update api_document set user_id = %s where user_id is null and project_id = %s limit %s",
                             [userId, value_projectId_distributio, int(value_number_distributio)])
    cursor.close()
    result_json = ""
    if len(res) != (int(value_number_distributio) * len(userId_List)):
        result_json = {"code": status.HTTP_400_BAD_REQUEST, "message": "数量不足,已分配" + str(len(res)) + "个", "data": "0"}
        return JsonResponse(result_json, status=status.HTTP_200_OK)
    else:
        result_json = {"code": status.HTTP_200_OK, "message": "操作成功", "data": "0"}
        return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


def UpdSequenceannotation(request):
    value_documentId_sequenceannotation = request.GET.get('documentId')
    value_labelId1_sequenceannotation = request.GET.get('labelId')
    value_userId_sequenceannotation = request.GET.get('userId')
    try:
        docList = Document.objects.filter(id=value_documentId_sequenceannotation, user_id=value_userId_sequenceannotation)
        if docList.count() > 0:
            labelInfo = Label.objects.get(id=value_labelId1_sequenceannotation)
            if labelInfo.slot_flg == 0:
                SequenceAnnotation.objects.filter(document_id=value_documentId_sequenceannotation, label_id1=None).delete()

            SequenceAnnotation.objects.filter(document_id=value_documentId_sequenceannotation, label_id=None).delete()
            SequenceAnnotation.objects.create(label_id1=value_labelId1_sequenceannotation, document_id=value_documentId_sequenceannotation, start_offset=0,
                                              end_offset=0, prob=0, manual=0, user_id=value_userId_sequenceannotation)

            result_json = {"code": status.HTTP_200_OK, "message": "操作成功", "data": "0"}
        else:
            result_json = {"code": status.HTTP_200_OK, "message": "操作成功", "data": "0", "warning": "请不要跳页,操作不属于自己的数据"}
        return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)
    except Exception:
        result_json = {"code": status.HTTP_500_INTERNAL_SERVER_ERROR, "message": "操作失败", "data": "0"}
        return JsonResponse(result_json, status=status.HTTP_500_INTERNAL_SERVER_ERROR, safe=False)


def GetSequenceannotationLabel1(request):
    #  获取前端传的docId
    value_documentId_sequenceannotation = request.GET.get('documentId')
    # 根据docId 获取标注文本信息
    docInfo = Document.objects.get(id=value_documentId_sequenceannotation)
    if docInfo.doc_ann is not None:
        # 根据标注文本信息 ann 查询意图类别方便用户 选择意图
        annShort = list(Label.objects.filter(p_text__isnull=True, project_id=3, text__icontains=docInfo.doc_ann).values_list('label_name', flat=True))
    else:
        annShort = ["暂无推荐"]
    # 获取复杂标注 意图信息
    res = SequenceAnnotation.objects.filter(document_id=int(value_documentId_sequenceannotation), label_id1__isnull=False)
    #  获取复杂标注  插槽信息
    biaoz_flg = SequenceAnnotation.objects.filter(document_id=int(value_documentId_sequenceannotation), label_id1__isnull=True)
    # 存在意图信息
    if len(res) > 0:
        # 意图id为空
        if int(res[0].label_id1) == 0:
            # 存在插槽信息
            if len(biaoz_flg) > 0:
                data = {"id": int(value_documentId_sequenceannotation), "label_id1": 0, "pTextId": 0, "slot_flg": True, "taggFlg": True, "docAnn": annShort}
            else:
                data = {"id": int(value_documentId_sequenceannotation), "label_id1": 0, "pTextId": 0, "slot_flg": True, "taggFlg": False, "docAnn": annShort}
        else:
            # 获取插槽信息
            label1_id = Label.objects.get(id=int(res[0].label_id1))
            # 根据意图 P-text获取 意图类别信息
            val = Label.objects.get(text=str(label1_id.p_text), project_id=int(label1_id.project_id))
            if len(biaoz_flg) > 0:
                data = {"id": int(value_documentId_sequenceannotation), "label_id1": int(res[0].label_id1), "pTextId": int(val.id), "slot_flg": label1_id.slot_flg, "taggFlg": True, "docAnn": annShort}
            else:
                data = {"id": int(value_documentId_sequenceannotation), "label_id1": int(res[0].label_id1),
                        "pTextId": int(val.id), "slot_flg": label1_id.slot_flg, "taggFlg": False, "docAnn":annShort}
    else:
        if len(biaoz_flg) > 0:
            data = {"id": int(value_documentId_sequenceannotation), "label_id1": 0, "pTextId": 0, "slot_flg": True, "taggFlg": True, "docAnn": annShort}
        else:
            data = {"id": int(value_documentId_sequenceannotation), "label_id1": 0, "pTextId": 0, "slot_flg": True,
                    "taggFlg": False, "docAnn": annShort}
    result_json = {"code": status.HTTP_200_OK, "message": "操作成功", "data": data}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


# 撤销标注的列表
def CancelAnnotationList(request):
    projectId = request.GET.get('projectId')
    startTime = request.GET.get('startTime')
    endTime = request.GET.get('endTime')
    flag = True
    # 时间比对   startTime     endTime           操作
    #              1             0          查询当天数据
    #              1             1          查询时间段数据
    #              0             0          默认查询到当前时间所有数据
    if startTime is None or startTime == '':
        startTime = "1970-01-01 00:00:00"
    else:
        if endTime is None or endTime == '':
            startTime = startTime + " 00:00:00"
            endTime = startTime[:-8] + "23:59:59"
            flag = False

    if endTime is None or endTime == '' and flag:
        endTime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
    userId = request.GET.get('userId')
    text = request.GET.get('q')
    limit = int(request.GET.get('limit'))
    offset = int(request.GET.get('offset'))
    likeText = '%'+ text + '%'
    dateFormat = '%Y-%m-%d %H:%m:%i'

    qualityStatus = request.GET.get('qualityStatus')  #质检标识 1 驳回数据 2 通过数据 0 未质检数据
    if qualityStatus is None or qualityStatus == '':
        qualityStatus = 0
    # 查询项目信息，查不到返回404
    project = get_object_or_404(Project, id=projectId)
    # 普通标记文本
    if project.project_type == 'DocumentClassification':
        if text is None or text == '':
            cursor = connection.cursor()
            cursor.execute(
                " SELECT label.label_name AS intent,doc.text AS document,label.label_name AS deliverable,doc.id AS documentId,label.id as annotationId,user.username AS userName,user.id AS userId,DATE_FORMAT(doca.updated_at,%s) AS updateTime FROM api_document AS doc LEFT JOIN api_documentannotation AS doca ON doc.id = doca.document_id LEFT JOIN api_label AS label ON doca.label_id = label.id LEFT JOIN auth_user AS user on user.id = doca.user_id WHERE doc.project_id = %s and doc.quality_status = %s AND doca.updated_at BETWEEN %s AND %s order by doca.updated_at desc,case  when doc.quality_status = 0 then 1 when doc.quality_status  = 1 then 2 when doc.quality_status  = 2 then 3 end  LIMIT %s,%s",
                [dateFormat, projectId, qualityStatus, startTime, endTime, offset, limit])
            initRes = dictfetchall(cursor)
            cursor.execute(
                " SELECT count(1) FROM api_document AS doc LEFT JOIN api_documentannotation AS doca  ON doc.id = doca.document_id LEFT JOIN api_label AS label ON doca.label_id = label.id LEFT JOIN auth_user AS user on user.id = doca.user_id WHERE doc.project_id = %s  and doc.quality_status = %s AND doca.updated_at BETWEEN %s AND %s",
                [projectId, qualityStatus, startTime, endTime])
            count = cursor.fetchall()[0][0]
            cursor.close()
        else:
            cursor = connection.cursor()
            cursor.execute(
                " SELECT label.label_name AS intent,doc.text AS document,label.label_name AS deliverable,doc.id AS documentId,label.id as annotationId,user.username AS userName,user.id AS userId,DATE_FORMAT(doca.updated_at,%s) AS updateTime FROM api_document AS doc LEFT JOIN api_documentannotation AS doca  ON doc.id = doca.document_id  LEFT JOIN api_label AS label ON doca.label_id = label.id LEFT JOIN auth_user AS user on user.id = doca.user_id WHERE doc.project_id = %s  AND (doc.text like %s or user.username like %s)  and doc.quality_status = %s  AND doca.updated_at BETWEEN %s AND %s order by doca.updated_at desc,case  when doc.quality_status = 0 then 1 when doc.quality_status  = 1 then 2 when doc.quality_status  = 2 then 3 end LIMIT %s,%s",
                [dateFormat, projectId, likeText, likeText, qualityStatus, startTime, endTime, offset, limit])
            initRes = dictfetchall(cursor)
            cursor.close()
            cursor = connection.cursor()
            cursor.execute(
                " SELECT count(1) FROM api_document AS doc LEFT JOIN api_documentannotation AS doca  ON doc.id = doca.document_id LEFT JOIN api_label AS label ON doca.label_id = label.id LEFT JOIN auth_user AS user on user.id = doca.user_id WHERE doc.project_id = %s  AND (doc.text like %s or user.username like %s) and doc.quality_status = %s AND doca.updated_at BETWEEN %s AND %s",
                [projectId, likeText, likeText, qualityStatus, startTime, endTime])
            count = cursor.fetchall()[0][0]
            cursor.close()
    # 复杂标记文本
    if project.project_type == 'SequenceLabeling':
        if text is None or text == '':
            # 执行sql查询
            cursor = connection.cursor()
            # # 查询符合条件的标注信息
            # cursor.execute(
            #     "SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1 LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where doc.project_id = %s and doc.quality_status = %s  AND seq.updated_at BETWEEN %s AND %s ",
            #     [projectId, qualityStatus, startTime, endTime])
            # documents = cursor.fetchall()
            # sql查询大分组
            # 带分页，排序，不带标注信息
            cursor.execute("SELECT doc.text AS document,seq.document_id AS documentId,seq.user_id AS userId,`us`.username AS userName,DATE_FORMAT(MAX(seq.updated_at),%s) AS updateTime,'' as deliverable FROM api_document as doc LEFT JOIN  api_sequenceannotation as seq ON doc.id = seq.document_id  LEFT JOIN api_label label ON label.id = seq.label_id LEFT JOIN auth_user AS us ON `us`.id = seq.user_id WHERE doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s GROUP BY doc.id order by seq.updated_at desc,case  when doc.quality_status = 0 then 1 when doc.quality_status  = 1 then 2 when doc.quality_status  = 2 then 3 end  LIMIT %s,%s",
            [dateFormat, projectId, qualityStatus, startTime, endTime, offset, limit])
            initRes = dictfetchall(cursor)
            documentParam = [projectId, qualityStatus, startTime, endTime]
            for documentInfo in initRes:
                documentParam.append(documentInfo["documentId"])
            str_in = '(' + ','.join(["%s"] * (len(documentParam) - 4)) + ")"
            if str_in == '()':
                sql = 'SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1 LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where doc.project_id = %s and doc.quality_status = %s  AND seq.updated_at BETWEEN %s AND %s'.format(str_in)
            else:
                sql = 'SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1 LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where doc.project_id = %s and doc.quality_status = %s  AND seq.updated_at BETWEEN %s AND %s and seq.document_id in {}'.format(str_in)
            # 查询符合条件的标注信息
            cursor.execute(sql,documentParam)
            documents = cursor.fetchall()
            #  sql 总数
            cursor.execute("SELECT COUNT(DISTINCT doc.id) FROM api_document as doc LEFT JOIN  api_sequenceannotation as seq ON doc.id = seq.document_id  LEFT JOIN api_label label ON label.id = seq.label_id LEFT JOIN auth_user AS us ON `us`.id = seq.user_id WHERE doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s",
            [projectId, qualityStatus, startTime, endTime])
            count = cursor.fetchall()[0][0]
            cursor.close()
        else:
            # 执行sql查询
            cursor = connection.cursor()
            # cursor.execute("SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1  LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where (doc.text like %s or us.username like %s) AND doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s",
            # [likeText, likeText, projectId, qualityStatus, startTime, endTime])
            # documents = cursor.fetchall()
            # sql查询大分组
            cursor.execute("SELECT doc.text AS document,seq.document_id AS documentId,seq.user_id AS userId,`us`.username AS userName,DATE_FORMAT(MAX(seq.updated_at),%s) AS updateTime,'' as deliverable FROM api_document as doc LEFT JOIN  api_sequenceannotation as seq ON doc.id = seq.document_id LEFT JOIN api_label label ON label.id = seq.label_id LEFT JOIN auth_user AS us ON `us`.id = seq.user_id WHERE (doc.text like %s or us.username like %s) AND doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s GROUP BY doc.id order by seq.updated_at desc,case  when doc.quality_status = 0 then 1 when doc.quality_status  = 1 then 2 when doc.quality_status  = 2 then 3 end LIMIT %s,%s",
            [dateFormat, likeText, likeText, projectId, qualityStatus, startTime, endTime, offset, limit])
            initRes = dictfetchall(cursor)
            documentParam = [likeText, likeText, projectId, qualityStatus, startTime, endTime]
            for documentInfo in initRes:
                documentParam.append(documentInfo["documentId"])
            str_in = '(' + ','.join(["%s"] * (len(documentParam) - 6)) + ")"
            if str_in == '()':
                sql = 'SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1  LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where (doc.text like %s or us.username like %s) AND doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s'.format(
                    str_in)
            else:
                sql = 'SELECT intent.label_name as intentions,doc.text as document,label.label_name as labelName,seq.document_id as documentId,seq.start_offset as startOffset,seq.end_offset as endOffset,label.background_color as textColor,intent.slot_flg as slotFlag FROM api_document as doc RIGHT JOIN  api_sequenceannotation as seq on doc.id = seq.document_id left join api_label label on label.id = seq.label_id left join api_label intent on intent.id = seq.label_id1  LEFT JOIN auth_user AS us ON `us`.id = seq.user_id where (doc.text like %s or us.username like %s) AND doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s and seq.document_id in {}'.format(
                    str_in)
            # 查询符合条件的标注信息
            cursor.execute(sql, documentParam)
            documents = cursor.fetchall()
            #  sql 总数
            cursor.execute("SELECT COUNT(DISTINCT doc.id) FROM api_document as doc LEFT JOIN  api_sequenceannotation as seq ON doc.id = seq.document_id LEFT JOIN api_label label ON label.id = seq.label_id LEFT JOIN auth_user AS us ON `us`.id = seq.user_id WHERE (doc.text like %s or us.username like %s) AND doc.project_id = %s and doc.quality_status = %s AND seq.updated_at BETWEEN %s AND %s",
            [likeText, likeText, projectId, qualityStatus, startTime, endTime])
            count = cursor.fetchall()[0][0]
            cursor.close()
        documentsDict = {}
        # sql 数据查询结果 —> 意图,文本,标签,文本id,标签开始下标,标签结束下标,标签颜色,插槽标识，page
        # 把相同文本多种标签的放进字典里
        dictDeliverable = {}
        for d in documents:
           if str(d[3]) not in documentsDict.keys():
               se = set()
               se.add(d)
               documentsDict[str(d[3])] = se
           else:
               documentsDict[str(d[3])].add(d)
        dictDeliverableList = []
        # 遍历相同的文本，输出对应格式
        for key in documentsDict.keys():
           # 把集合转成列表根据下标取文本数据，初始化新列表长度
           docSet = documentsDict[key]
           docList = list(docSet)
           # 定义好长度，用列表下标快速插入
           slots = [''] * len(docList[0][1])
           document = ''
           markedList = []
           for docDetail in documentsDict[key]:
               if docDetail[2] is None or docDetail[2] == "":
                   if document == "" and len(documentsDict[key]) == 1:
                       document = docDetail[1]
                   intent = docDetail[0]
                   slotFlag = docDetail[7]
               else:
                   document = "|".join(docDetail[1])
                   # 标签不是 O 时
                   subscript = docDetail[4]
                   if docDetail[5] is None:
                       endIndex = 0
                   elif docDetail[5] > len(docList[0][1]):
                       endIndex = len(docList[0][1])
                   else:
                       endIndex = docDetail[5]
                   if not docDetail[2] == 'O':
                       for item in range(endIndex - (0 if docDetail[4] is None else docDetail[4])):
                           slotInfo = docDetail[2] + '|'
                           slots.insert(subscript, slotInfo)
                           subscript += 1
                   # 标签是 O 时
                   else:
                       for item in range(endIndex - (0 if docDetail[4] is None else docDetail[4])):
                           slots.insert(subscript, docDetail[2] + '|')
                           subscript += 1
           indexFlag = 0
           for itemIndex in range(len(docList[0][1])):
               flag = True
               for text in docList:
                   if text[5] > len(text[1]):
                       endIndex = len(text[1])
                   else:
                       endIndex = text[5]
                   if text[4] == itemIndex and text[6] is not None and indexFlag == 0:
                       markedList.append({'color': text[6], 'text': docList[0][1][itemIndex:endIndex], 'marked':text[2]})
                       flag = False
                       indexFlag = endIndex
                   elif text[4] == indexFlag and text[6] is not None:
                       markedList.append({'color': text[6], 'text': docList[0][1][indexFlag:endIndex], 'marked':text[2]})
                       flag = False
                       indexFlag = endIndex
               if flag:
                   if indexFlag != 0:
                       markedList.append({'color': '', 'text': docList[0][1][indexFlag], 'marked':''})
                       indexFlag += 1
                   else:
                       markedList.append({'color': '', 'text': docList[0][1][itemIndex], 'marked':''})
               if indexFlag == len(docList[0][1]):
                   break
           # 添加到文本信息
           slot = "".join(slots)[:-1]
           meta = str(intent) + "\t" + str(document) + "\t" + str(slot)
           dictDeliverable["documentId"] = docList[0][3]
           dictDeliverable["meta"] = meta
           dictDeliverable["intent"] = str(intent)
           dictDeliverable["marked"] = slot
           dictDeliverable["markedList"] = markedList
           dictDeliverable["slotFlag"] = slotFlag
           dictDeliverableList.append(dictDeliverable.copy())
        for item in initRes:
           for dic in dictDeliverableList:
               try:
                   if dic['documentId'] == item['documentId']:
                       item["deliverable"] = dic["meta"]
                       item["intent"] = dic["intent"]
                       item["marked"] = dic["marked"]
                       item["markedList"] = dic["markedList"]
                       item["slotFlag"] = dic["slotFlag"]
               except KeyError:
                   item["deliverable"] = ''
                   continue
    # 计算总页码
    if count % limit != 0 and count > limit:
        totalPage = count // limit + 1
    elif count % limit != 0 and count <= limit:
        totalPage = count // limit + 1
    else:
        totalPage = count // limit
    # 计算当前页码
    if (offset+1) % limit != 0 and (offset+1) > limit:
        currentPage = (offset+1) // limit + 1
    elif (offset+1) % limit != 0 and (offset+1) <= limit:
        currentPage = (offset+1) // limit + 1
    else:
        currentPage = (offset+1) // limit

    # 查找未分配已标记数据  进行分配
    for item in initRes:
        cursor = connection.cursor()
        cursor.execute(
            "SELECT user_id FROM api_document WHERE id = %s ",
            [item["documentId"]])
        localUserId = dictfetchall(cursor)[0]["user_id"]
        if localUserId is None:
            Document.objects.filter(id=item["documentId"]).update(user_id=item["userId"])

    # 定义page参数
    for item in initRes:
        # 循环查询数据
        # 数据为普通文本时
        if project.project_type == 'DocumentClassification':
            if item["userId"] == 1 or item["userId"] == 2:
                cursor = connection.cursor()
                cursor.execute(
                    "select sort_no from ( SELECT a.*,@rank :=@rank + 1 AS sort_no FROM ( SELECT id FROM api_document WHERE project_id = %s order by id) a,(SELECT @rank := 0) b) sort where id = %s",
                    [projectId, item["documentId"]])
                page = dictfetchall(cursor)
                cursor.close()
            else:
                cursor = connection.cursor()
                cursor.execute(
                    "select sort_no from ( SELECT a.*,@rank :=@rank + 1 AS sort_no FROM ( SELECT id FROM api_document WHERE project_id = %s and user_id = %s order by id) a,(SELECT @rank := 0) b) sort where id = %s",
                    [projectId, item["userId"], item["documentId"]])
                page = dictfetchall(cursor)
                cursor.close()
        # 数据为复杂文本时
        else:
            if item["userId"] == 1 or item["userId"] == 2:
                cursor = connection.cursor()
                cursor.execute(
                    "SELECT user_id FROM api_document WHERE id = %s ",
                    [item["documentId"]])
                localUserId = dictfetchall(cursor)[0]["user_id"]
                if localUserId is not None:
                    item["userId"] = localUserId
                else:
                    localUserId = item["userId"]
                if localUserId == 1 or localUserId == 2:
                    cursor.execute(
                        "select sort_no from ( SELECT a.*,@rank :=@rank + 1 AS sort_no FROM ( SELECT id FROM api_document WHERE project_id = %s order by id) a,(SELECT @rank := 0) b) sort where id = %s",
                        [projectId, item["documentId"]])
                    page = dictfetchall(cursor)
                    cursor.close()
                else:
                    cursor.execute(
                        "select sort_no from ( SELECT a.*,@rank :=@rank + 1 AS sort_no FROM ( SELECT id FROM api_document WHERE project_id = %s and user_id = %s order by id) a,(SELECT @rank := 0) b) sort where id = %s",
                        [projectId, localUserId, item["documentId"]])
                    page = dictfetchall(cursor)
                    cursor.close()
            else:
                localUserId = item["userId"]
                cursor = connection.cursor()
                cursor.execute(
                    "select sort_no from ( SELECT a.*,@rank :=@rank + 1 AS sort_no FROM ( SELECT id FROM api_document WHERE project_id = %s and user_id = %s order by id) a,(SELECT @rank := 0) b) sort where id = %s",
                    [projectId, localUserId, item["documentId"]])
                page = dictfetchall(cursor)
                cursor.close()
        if len(page) == 1:
            item["page"] = int(page[0]["sort_no"])
        else:
            item["page"] = -1
            print('{' + str(item["document"]) + '}未被分配给{' + str(item["userName"]) + '},导致定义分页参数失败!  userId为：' + str(localUserId) + '，文本id为：' + str(item["documentId"]) )
    # 定义返回参数
    res = {"results": initRes, "count": count, "currentPage":currentPage, "limit":limit, "totalPage":totalPage, "offset":offset }
    return JsonResponse(res, safe=False)


# 撤销标注
def CancelAnnotation(request):
    # projectId = request.GET.get('projectId')
    # documentId = request.GET.get('documentId')
    # 查询项目信息，查不到返回404
    # project = get_object_or_404(Project, id=projectId)
    # # 普通标记文本
    # if project.project_type == 'DocumentClassification':
    #     # TODO 当前物理删除，后续可能会改为逻辑删除
    #     DocumentAnnotation.objects.filter(document_id__in=documentId.split(',')).delete()
    #
    # # 复杂标记文本
    # if project.project_type == 'SequenceLabeling':
    #     # TODO 当前物理删除，后续可能会改为逻辑删除
    #     SequenceAnnotation.objects.filter(document_id__in=documentId.split(',')).delete()

    # Document.objects.filter(id__in=documentId.split(',')).update(user_id=None)

    result_json = {"code": status.HTTP_200_OK, "message": "操作成功"}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


# 质检  驳回/通过操作
def QualityOperation(request):
    documentId = request.GET.get('documentId')
    opType = request.GET.get('opType')
    userId = request.GET.get('userId')
    print(documentId)

    if opType == '1':
        # 驳回操作
        Document.objects.filter(id__in=documentId.split(',')).update(quality_user_id=userId,quality_status=1)
    else:
        # 通过操作
        Document.objects.filter(id__in=documentId.split(',')).update(quality_user_id=userId,quality_status=2)

    result_json = {"code": status.HTTP_200_OK, "message": "操作成功"}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


def GetLabels(request):
    members = []
    value_label = Label.objects.annotate(name=F("label_name")).filter(project_id=request.GET.get("projectId"), p_text=None).all().values()
    for member in value_label:
        var = Label.objects.annotate(name=F("label_name")).filter(p_text=member.get("text"), project_id=request.GET.get("projectId")).all().values()
        member['children'] = list(var)
        members.append(member)
    result_json = {"code": status.HTTP_200_OK, "message": "操作成功", "data": members}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


def BatchUpdateLabels(request):
    labelId = request.GET.get('labelId')
    p_text = request.GET.get('p_text')

    Label.objects.filter(id__in=labelId.split(",")).update(p_text=p_text)

    result_json = {"code": status.HTTP_200_OK, "message": "操作成功"}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)


def QueryCompletedCount(request):
    # 调用删除数据的接口
    DeleteErrorData(request)
    global maxPage
    projectId = request.GET.get('projectId')
    userId = request.GET.get('userId')
    # 查询项目信息，查不到返回404
    project = get_object_or_404(Project, id=projectId)
    # 普通标记文本
    if project.project_type == 'DocumentClassification':
        cursor = connection.cursor()
        cursor.execute(
            "SELECT COUNT(doc.id) as count FROM api_document doc WHERE doc.project_id = %s  AND (doc.user_id = %s or doc.user_id is  null)  AND doc.id <= (SELECT MAX(doc.id)FROM api_document doc INNER JOIN api_documentannotation doca ON doc.id = doca.document_id WHERE doc.project_id = %s  AND doc.user_id = %s )	AND doc.id >= (SELECT MIN(doc.id)FROM api_document doc INNER JOIN api_documentannotation doca ON doc.id = doca.document_id WHERE doc.project_id = %s  AND doc.user_id = %s )",
            [projectId, userId,projectId, userId,projectId, userId])
        maxPage = dictfetchall(cursor)[0]['count']
        cursor.close()

    # 复杂标记文本
    if project.project_type == 'SequenceLabeling':
        # cursor = connection.cursor()
        # cursor.execute(
        #     "SELECT doc.id as docId FROM api_document doc WHERE doc.project_id = %s AND (doc.user_id = %s or doc.user_id is null) order by doc.id ",
        #     [projectId, userId])
        # maxPage = dictfetchall(cursor)
        # cursor.execute(
        #     "SELECT MAX(doca.document_id) as docId FROM api_document doc LEFT JOIN  api_sequenceannotation doca ON doc.id = doca.document_id WHERE doc.project_id = %s AND doc.user_id = %s ",
        #     [projectId, userId])
        # maxDocument = dictfetchall(cursor)[0]
        # cursor.close()
        cursor = connection.cursor()
        cursor.execute(
            "SELECT COUNT(doc.id) as count FROM api_document doc WHERE doc.project_id = %s  AND (doc.user_id = %s or doc.user_id is  null) AND doc.id <= (SELECT MAX(doc.id)FROM api_document doc INNER JOIN api_sequenceannotation doca ON doc.id = doca.document_id and doca.label_id is null WHERE doc.project_id = %s  AND doc.user_id = %s )	AND doc.id >= (SELECT MIN(doc.id)FROM api_document doc INNER JOIN api_sequenceannotation doca ON doc.id = doca.document_id and doca.label_id is null WHERE doc.project_id = %s  AND doc.user_id = %s )",
            [projectId, userId, projectId, userId, projectId, userId])
        maxPage = dictfetchall(cursor)[0]['count']
        cursor.close()
    # page = 1
    # for item in maxPage:
    #     if item['docaId'] == maxDocument['docId']:
    #         break
    #     else:
    #         page += 1
    return JsonResponse({'completedAnnotationCount': maxPage if maxPage != 0 else 1}, status=status.HTTP_200_OK, safe=False)


def DeleteErrorData(request):
    projectId = request.GET.get('projectId')
    # 查询项目信息，查不到返回404
    project = get_object_or_404(Project, id=projectId)
    # 普通标记文本
    if project.project_type == 'DocumentClassification':
        cursor = connection.cursor()
        cursor.execute(
            "SELECT	doca.document_id FROM	api_document doc RIGHT JOIN api_documentannotation doca ON doca.document_id = doc.id WHERE	doc.project_id = %s AND doc.user_id IS NULL",
            [projectId])
        documents = dictfetchall(cursor)
        cursor.close()

    # 复杂标记文本
    if project.project_type == 'SequenceLabeling':
        cursor = connection.cursor()
        cursor.execute(
            "SELECT	doca.document_id FROM	api_document doc RIGHT JOIN api_sequenceannotation doca ON doca.document_id = doc.id and doca.label_id is null WHERE doc.project_id = %s AND doc.user_id IS NULL",
            [projectId])
        documents = dictfetchall(cursor)
        cursor.close()

    # 循环删除错误数据
    for docId in documents:
        if docId['document_id'] is not None:
            DocumentAnnotation.objects.filter(document_id=docId['document_id']).delete()
            SequenceAnnotation.objects.filter(document_id=docId['document_id']).delete()

    result_json = {"code": status.HTTP_200_OK, "message": "操作成功"}
    return JsonResponse(result_json, status=status.HTTP_200_OK, safe=False)